# 该程序是用来将mysql中的sysid信息迁移到redis中的
import pymysql
import redis

# 这是mysql数据库的连接属性
MYSQL_CONNECT = {
    'MYSQL_HOST': '114.115.219.219',
    'MYSQL_PORT': 3306,
    'MYSQL_DB': 'globallegaldatabase',
    'MYSQL_USER': 'root',
    'MYSQL_PASSWD': 'xinanzhengfadaqxueapp03!',
    'MYSQL_CHARSET': 'utf8',
    'MYSQL_TABLE': 'lawtext'  # 这是数据库存储数据的表
}

# 这是redis数据库信息
REDIS_CONNECT = {
    'REDIS_HOST': '114.115.219.219',
    'REDIS_PORT': 6379,
    'REDIS_PASSWD': 'CNKIcnki614',
    'REDIS_DB': '1',
    'REDIS_KEY': 'Law'
}

# 连接数据库
host = MYSQL_CONNECT['MYSQL_HOST']
port = MYSQL_CONNECT['MYSQL_PORT']
db = MYSQL_CONNECT['MYSQL_DB']
user = MYSQL_CONNECT['MYSQL_USER']
password = MYSQL_CONNECT['MYSQL_PASSWD']
urlconn = pymysql.connect(host=host, port=port, db=db, user=user, passwd=password, charset='utf8')
redis_host = REDIS_CONNECT['REDIS_HOST']
redis_port = REDIS_CONNECT['REDIS_PORT']
redis_passwd = REDIS_CONNECT['REDIS_PASSWD']
redis_db = REDIS_CONNECT['REDIS_DB']
pool = redis.ConnectionPool(host=redis_host, port=redis_port, password=redis_passwd, db=redis_db, decode_responses=True)
# 创建游标测试连接数据库是否成功
urlcursor = urlconn.cursor()
sql = "SELECT DISTINCT SYSID FROM %s " % MYSQL_CONNECT['MYSQL_TABLE']
i = 0
try:
    urlcursor.execute(sql)
    rs = urlcursor.fetchall()
    rediscursor = redis.Redis(connection_pool=pool)
    for rl in rs:
        r = rl[0]
        try:
            rediscursor.sadd(REDIS_CONNECT['REDIS_KEY'], r)
            i = i+1
            print("\r已更新: %d" % i, end="")
        except Exception as e:
            print('网站id存入redis数据库异常:' + str(e))
    rediscursor.close()
except Exception as e:
    print('mysql查找数据异常：' + str(e))
urlcursor.close()
urlconn.close()
